package com.top.dao;

import com.top.entiy.Food;
import com.top.entiy.FoodCategory;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:
 * @DateTime: 2023/12/9 12:54
 * @Description: TODO
 */
public class FoodDao extends AbstractDAO implements IDAO<Integer, Food>{
    public Food findByName(String name) {
        String sql = "SELECT * FROM food where name = ? and `delete` = 0";
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1,name);
            ResultSet rs = statement.executeQuery();
            if(rs.next()){
                Food vo = new Food();
                vo.setId(rs.getInt("id"));
                vo.setName(rs.getString("name"));
                vo.setInfo(rs.getString("info"));
                vo.setPrice(rs.getBigDecimal("price"));
                vo.setCategory(rs.getInt("category"));
                vo.setEnable(rs.getInt("enable"));
                vo.setHot(rs.getInt("hot"));
                vo.setCreateTime(rs.getDate("create_time"));
                vo.setUpdateTime(rs.getDate("upate_time"));
                vo.setDelete(rs.getInt("delete"));
                vo.setHotStr(rs.getString("hot_str"));
                vo.setEnableStr(rs.getString("enable_str"));
                return vo;
            }
        }catch (SQLException e) {
            System.err.println("查询失败"+e);
        }
        return null;
    }

    @Override
    public boolean doCreate(Food vo){
        String sql = "INSERT INTO `ordering_food`.`food` " +
                "( `name`, `price`, `info`, `hot`, `enable`, `delete`, `create_time`, `update_time`)" +
                " VALUES ( ?, ?, ?, ?, ?, ?, now(), now());";
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1,vo.getName());
            statement.setBigDecimal(2,vo.getPrice());
            statement.setString(3,vo.getInfo());
            statement.setInt(4,vo.getHot());
            statement.setInt(5,vo.getEnable());
            statement.setInt(6,vo.getDelete());
            return statement.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("创建失败"+e);
        }
        return false;
    }

    @Override
    public boolean doUpdate(Food vo){
        try {
            String sql = "UPDATE food SET `name`=? ,price = ?,info=?,hot=?,`category` = ? WHERE id = ?";
            statement =conn.prepareStatement(sql);
            statement.setString(1,vo.getName());
            statement.setBigDecimal(2,vo.getPrice());
            statement.setString(3,vo.getInfo());
            statement.setInt(4,vo.getHot());
            statement.setInt(5,vo.getCategory());
            statement.setInt(6,vo.getId());
            return statement.executeUpdate()>0;
        } catch (SQLException e) {
            System.err.println("更新失败"+e);
        }
        return false;
    }

    @Override
    public boolean doRemove(Integer id) {
        try {
            String sql = "UPDATE food SET `delete`=1 WHERE id = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1,id);
            return statement.executeUpdate()>0;
        }catch (SQLException ex) {
            System.err.println("删除失败"+ex);
        }
        return false;
    }

    @Override
    public Food findById(Integer id)  {
        try {
            String sql = "SELECT * FROM food WHERE id=? and `delete` = 0";
            statement =conn.prepareStatement(sql);
            statement.setInt(1,id);
            ResultSet rs = statement.executeQuery();
            if(rs.next()){
                Food vo = new Food();
                vo.setId(rs.getInt("id"));
                vo.setName(rs.getString("name"));
                vo.setInfo(rs.getString("info"));
                vo.setPrice(rs.getBigDecimal("price"));
                vo.setCategory(rs.getInt("category"));
                vo.setEnable(rs.getInt("enable"));
                vo.setHot(rs.getInt("hot"));
                vo.setCreateTime(rs.getDate("create_time"));
                vo.setUpdateTime(rs.getDate("update_time"));
                vo.setDelete(rs.getInt("delete"));
                return vo;
            }
        } catch (SQLException e) {
            System.err.println("查询失败"+e);
        }
        return null;
    }

    @Override
    public List<Food> findAll() {
        List<Food> all = new ArrayList<>();
        try {
            String sql = "SELECT f.*,c.category_name as categoryName \n" +
                    "FROM food f\n" +
                    "LEFT JOIN food_category c ON c.id=f.category \n" +
                    "where `delete` = 0 ORDER BY id desc";
            statement = conn.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                Food food = new Food();
                food.setId(rs.getInt("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getBigDecimal("price"));
                food.setCategory(rs.getInt("category"));
                food.setHotStr(rs.getInt("hot")==1?"热销":"-");
                food.setEnable(rs.getInt("enable"));
                food.setEnableStr(rs.getInt("enable")==1?"上架":"下架");
                food.setHot(rs.getInt("hot"));
                food.setInfo(rs.getString("info"));
                food.setCategoryName(rs.getString("categoryName"));
                food.setCreateTime(rs.getDate("create_time"));
                food.setUpdateTime(rs.getDate("update_time"));
                all.add(food);
            }
            return all;
        } catch (SQLException e) {
            System.err.println("查询失败");
        }
        return all;
    }

    @Override
    public List<Food> findAllBySplit(String column, String keyWord, Integer currentPage, Integer lineSize) throws SQLException {
        return null;
    }

    @Override
    public Integer getAllCount(String column, String keyWord) throws SQLException {
        return null;
    }

    /**
     * 查询热销菜品
     * @return
     */
    public List<Food> findAllByHot() {
        try {
            List<Food> all = new ArrayList<>();
            String sql = "SELECT f.*,c.category_name as categoryName \n" +
                    "FROM food f\n" +
                    "LEFT JOIN food_category c ON c.id=f.category \n" +
                    "where `delete` = 0 and hot = 1 and enable = 1 ORDER BY id desc";
            statement = conn.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                Food food = new Food();
                food.setId(rs.getInt("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getBigDecimal("price"));
                food.setCategory(rs.getInt("category"));
                food.setHotStr(rs.getInt("hot")==1?"热销":"-");
                food.setEnable(rs.getInt("enable"));
                food.setEnableStr(rs.getInt("enable")==1?"上架":"下架");
                food.setHot(rs.getInt("hot"));
                food.setInfo(rs.getString("info"));
                food.setCategoryName(rs.getString("categoryName"));
                food.setCreateTime(rs.getDate("create_time"));
                food.setUpdateTime(rs.getDate("update_time"));
                all.add(food);
            }
            return all;
        } catch (SQLException e) {
            System.err.println("查询失败");
        }
        return null;
    }

    /**
     * 查询全部菜品 根据分类名称排序
     * @return
     */
    public List<Food> findAllOrderByCategoryName() {
        List<Food> all = new ArrayList<>();
        try {
            String sql = "SELECT f.*,c.category_name as categoryName \n" +
                    "FROM food f\n" +
                    "LEFT JOIN food_category c ON c.id=f.category \n" +
                    "where `delete` = 0 and enable = 1 ORDER BY category desc";
            statement = conn.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                Food food = new Food();
                food.setId(rs.getInt("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getBigDecimal("price"));
                food.setCategory(rs.getInt("category"));
                food.setHotStr(rs.getInt("hot")==1?"热销":"-");
                food.setEnable(rs.getInt("enable"));
                food.setEnableStr(rs.getInt("enable")==1?"上架":"下架");
                food.setHot(rs.getInt("hot"));
                food.setInfo(rs.getString("info"));
                food.setCategoryName(rs.getString("categoryName"));
                food.setCreateTime(rs.getDate("create_time"));
                food.setUpdateTime(rs.getDate("update_time"));
                all.add(food);
            }
            return all;
        } catch (SQLException e) {
            System.err.println("查询失败");
        }
        return all;
    }

    public List<Food> findAllByCategoryId(int next) {
        List<Food> all = new ArrayList<>();
        try {
            String sql = "SELECT f.*,c.category_name as categoryName \n" +
                    "FROM food f\n" +
                    "LEFT JOIN food_category c ON c.id=f.category \n" +
                    "where `delete` = 0 and category = ? ORDER BY id desc";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, next);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                Food food = new Food();
                food.setId(rs.getInt("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getBigDecimal("price"));
                food.setCategory(rs.getInt("category"));
                food.setHotStr(rs.getInt("hot")==1?"热销":"-");
                food.setEnable(rs.getInt("enable"));
                food.setEnableStr(rs.getInt("enable")==1?"上架":"下架");
                food.setHot(rs.getInt("hot"));
                food.setInfo(rs.getString("info"));
                food.setCategoryName(rs.getString("categoryName"));
                food.setCreateTime(rs.getDate("create_time"));
                food.setUpdateTime(rs.getDate("update_time"));
                all.add(food);
            }
            return all;
        } catch (SQLException e) {
            System.err.println("查询失败");
        }
        return all;
    }
}
